package com.flyme.excel.load;

import com.flyme.excel.validator.ExcelCellValidator;
import com.flyme.excel.validator.ExcelCellValidators;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.mmc.tools.util.ReflectUtil;
import com.uc.common.utils.time.DateFormatUtil;
import com.ycc.vins.util.ValidateResult;
import com.zuche.framework.common.SpringApplicationContext;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ExcelImportUtil {
    /**
     * 一次导入最大量5000条
     */
    private static Integer maxSize = 5000;
    private static Logger logger = LoggerFactory.getLogger(ExcelImportUtil.class);

    /**
     * 获取单元格数据  返回字符串
     *
     * @param cell
     */
    public static String getCellValue2String(Cell cell) {
        String str = "";
        if (cell == null) {
            return str;
        }

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                Double nbvalue = cell.getNumericCellValue();
                if (nbvalue != null) {
                    if (nbvalue.intValue() - nbvalue == 0) {
                        str = String.valueOf(nbvalue.intValue());
                    } else {
                        str = String.valueOf(nbvalue);
                    }

                }
                //读取日期
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    double d = cell.getNumericCellValue();
                    Date date = HSSFDateUtil.getJavaDate(d);
                    str = DateFormatUtil.formatDate(DateFormatUtil.PATTERN_ISO_ON_DATE, date);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                if (cell.getStringCellValue() != null) {
                    str = cell.getStringCellValue().trim();
                }
                break;
            default:
        }
        return str;
    }

    /**
     * 获取导入Excel 信息
     *
     * @param excelType
     * @param mf
     * @throws Exception
     */
    public static Workbook getExcelWorkbook(int excelType, MultipartFile mf) throws Exception {
        Workbook wb;
        if (excelType == 1) {
            wb = new HSSFWorkbook(mf.getInputStream());
        } else {
            wb = new XSSFWorkbook(mf.getInputStream());
        }
        return wb;
    }

    /**
     * 从Excel中获取数据
     * Excel首列的名称和字段的注解名称必须一一对应
     *
     * @param excelType
     * @param mf
     * @return
     * @throws Exception
     */
    public static List getDataList(int excelType, MultipartFile mf, Class c) throws ExcelImportException, Exception {
        Workbook workbook = getExcelWorkbook(excelType, mf);
        List list = new ArrayList();
        //取第一个sheet页数据
        Sheet sheet = workbook.getSheetAt(0);
        Row firstRow = sheet.getRow(0);
        Map<String, Field> mapField = getMapField(firstRow, c);
        List<String> columnIndexList = Lists.newArrayList(mapField.keySet());
        if (MapUtils.isEmpty(mapField)) {
            throw new ExcelImportException("excel注解字段为空");
        }
        if (sheet.getLastRowNum() > maxSize) {
            throw new ExcelImportException("一次导入数据不能超过" + maxSize + "条");
        }
        StringBuilder errMsg = new StringBuilder();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            if (!isBlankRow(row)) {
                //根据注解匹配字段
                Object obj = c.newInstance();
                for (int k = 0; k < row.getLastCellNum(); k++) {
                    Field f = mapField.get(getCellValue2String(firstRow.getCell(k)));
                    if (f != null) {
                        String type = f.getType().toString();
                        f.setAccessible(true);
                        Object value = null;
                        try {
                            value = getCellValue(row.getCell(k), type);
                        } catch (ExcelImportException e) {
                            errMsg.append("第" + (j + 1) + "行，第" + (k + 1) + "列，" + f.getAnnotation(ExcelImportField.class).cellName() + e.getMessage() + "\n");
                            continue;
                        }

                        f.set(obj, value);
                    }
                }

                List<String> propFields = ReflectUtil.getAllFields(obj.getClass());
                for (String propField : propFields) {

                    Field field = obj.getClass().getDeclaredField(propField);
                    ExcelImportField annotation = field.getAnnotation(ExcelImportField.class);
                    if (annotation == null) {
                        continue;
                    }

                    Object fieldValue = ReflectUtil.getFieldValue(obj, propField);
                    String validateValue = fieldValue == null ? null : String.valueOf(fieldValue);
                    Integer colIndex = columnIndexList.indexOf(annotation.cellName());

                    for (ExcelCellValidators validatorEnum : annotation.validatedBy()) {
                        if (validatorEnum.equals(ExcelCellValidators.VOID)) {
                            continue;
                        }
                        ValidateResult vRes = ((ExcelCellValidator) SpringApplicationContext.getBean(validatorEnum.getName())).apply(validateValue);
                        if (vRes.getResult().equals(ValidateResult.ResultEnum.NOT_PASS)) {
                            errMsg.append(" 第" + (j + 1) + "行，第" + (colIndex + 1) + "列，" + annotation.cellName() + vRes.getMessage());
                        }
                    }
                }
                list.add(obj);
            }
        }
        if (errMsg.length() > 0) {
            throw new ExcelImportException(errMsg.toString());
        }
        return list;
    }

    /**
     * 注解名称和field映射
     *
     * @param c
     * @return
     */
    private static Map<String, Field> getMapField(Row firstRow, Class c) {
        Map<String, Field> map = Maps.newLinkedHashMap();
        for (int k = 0; k < firstRow.getLastCellNum(); k++) {
            String annName = getCellValue2String(firstRow.getCell(k));
            Field[] fs = c.getDeclaredFields();
            for (int i = 0; i < fs.length; i++) {
                if (fs[i].getAnnotation(ExcelImportField.class) != null && fs[i].getAnnotation(ExcelImportField.class).cellName().equals(annName)) {
                    map.put(annName, fs[i]);
                    break;
                }
            }
        }
        return map;
    }

    /**
     * 判断excel是否有空白行
     *
     * @param row
     * @return
     */
    private static Boolean isBlankRow(Row row) {
        Boolean isBlank = true;
        if (row == null) {
            return isBlank;
        }
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                isBlank = false;
                break;
            }
        }
        return isBlank;
    }

    /**
     * 获取Excel 单元格的内容
     *
     * @param cell
     */
    public static Object getCellValue(Cell cell, String type) throws ExcelImportException {
        Object obj = null;
        String str = getCellValue2String(cell);
        try {
            if (type.contains("java.lang.Double") && StringUtils.isNotEmpty(str)) {
                obj = Double.parseDouble(str);
            } else if (type.contains("java.lang.Integer") && StringUtils.isNotEmpty(str)) {
                obj = Integer.parseInt(str);
            } else if (type.contains("java.lang.Long") && StringUtils.isNotEmpty(str)) {
                obj = Long.parseLong(str);
            } else if (type.contains("java.math.BigDecimal") && StringUtils.isNotEmpty(str)) {
                obj = new BigDecimal(str);
            } else if (type.contains("java.lang.String") && StringUtils.isNotEmpty(str)) {
                obj = str;
            }
        } catch (NumberFormatException e) {
            throw new ExcelImportException("格式不对" ,e);
        }
        return obj;
    }

    /**
     * 获取Excel模板
     *
     * @param selectColumns
     * @param sheetName
     * @return
     */
    public static InputStream getExcelTemplate(List<String> selectColumns, String sheetName) {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCellStyle headStyle = headStyle(wb);
        HSSFSheet sheet = wb.createSheet();
        sheet.setDefaultColumnWidth(20);
        wb.setSheetName(0, sheetName);
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 450);
        HSSFCell cell;

        for (int i = 0; i < selectColumns.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(selectColumns.get(i));
            cell.setCellStyle(headStyle);
        }
        // 将流读到内存里面
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            wb.write(os);
        } catch (IOException e) {
            logger.error("threw an exception" , e);
        }

        byte[] content = os.toByteArray();
        InputStream is = null;
        is = new ByteArrayInputStream(content);
        try {
            os.close();
        } catch (IOException e) {
            logger.error("threw an exception" , e);
        }
        return is;


    }

    /**
     * 设置导出到的excel的样式
     *
     * @param wb
     * @return
     */
    private static HSSFCellStyle headStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        HSSFFont f = wb.createFont();
        // 字号
        f.setFontHeightInPoints((short) 11);
        f.setBold(true);
        style.setFont(f);

        //背景颜色
//        style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
//        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 上下居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setWrapText(true);
        return style;
    }
}
